import pandas as pd
import pymysql

        
        
class mysqlHelper:
    def __init__(self, url, user, pwd):
        # self.localhost = "192.168.90.193"
        # self.localhost = "192.168.3.144"
        # self.localuser = "gmgbj"
        # self.localpwd = 'G7gIVYB9Xk^7'
        # self.aliyun_totalurl_trade = 'mysql+pymysql://dbuser:dbuser@121@rm-bp1witqzl76lpag957o.mysql.rds.aliyuncs.com:3306/Trade?charset=utf8'
        # self.aliyun_totalurl_strategy = 'mysql+pymysql://dbuser:dbuser@121@rm-bp1witqzl76lpag957o.mysql.rds.aliyuncs.com:3306/strategy?charset=utf8'
        # self.aliyun_url = "rm-bp1witqzl76lpag957o.mysql.rds.aliyuncs.com"
        # self.aliyun_usr = "dbuser"
        # self.aliyun_pwd = "dbuser@121"
        self.__url = url
        self.__user = user
        self.__pwd = pwd

    # def addTable_condition(self):
    #     conn = pymysql.connect(host=self.__url,
    #                            user=self.localuser,
    #                            passwd=self.localpwd,
    #                            db='tjz_trade',
    #                            port=3306,
    #                            charset='utf8')
    #     df = pd.read_sql('select * from condition_orders', conn)
    #     df = df.head(1)
    #     df.to_sql(
    #         name="condition_orders",
    #         con=self.__url,
    #         if_exists='append',  #'fail'，'replace'，'append'}，默认'fail' 
    #         index_label='ConditionId',
    #         index=False)

    # def addTableFromDB(self, fromtable, table, sql):
    #     conn = pymysql.connect(host=self.__url,
    #                            user=self.localuser,
    #                            passwd=self.localpwd,
    #                            db=fromtable,
    #                            port=3306,
    #                            charset='utf8')
    #     df = pd.read_sql(sql, conn)
    #     df.to_sql(name=table,
    #               con=self.aliyun_totalurl,
    #               if_exists='replace',
    #               index=False)

    # def createTableFromDt(self, table, df, dbname):
    #     url = self.aliyun_totalurl_trade
    #     if dbname == 'strategy':
    #         url = self.aliyun_totalurl_strategy

    #     df.to_sql(name=table, con=url, if_exists='replace', index=False)

    def createDB(self):
        db = pymysql.connect(host=self.__url,
                             user=self.__user,
                             passwd=self.__pwd,
                             port=3306,
                             charset='utf8')

        cursor = db.cursor()

        # 使用 execute()  方法执行 SQL 查询
        cursor.execute("CREATE database Trade")

        # 关闭数据库连接
        db.close()

    def getdata_to_df(self, sql, db='tjz_trade'):
        conn = pymysql.connect(host=self.__url,
                               user=self.__user,
                               passwd=self.__pwd,
                               db=db ,
                               port=3306,
                               charset='utf8')
        return pd.read_sql(sql, conn)
        